﻿CREATE PROCEDURE [dbo].[proc_Department_Create_News]
	(
	@DName nvarchar(50),
	@ParentId VARCHAR(100),
	@CompanyId VARCHAR(20),
	@Sort int,
	@ProtectCount int,
	@ProtectDays int,
	@TrackCount int,
	@TrackDays int
	)
AS

	--	DECLARE @Cid INT
	--DECLARE @Did VARCHAR(100)
	--declare @Did_temp varchar(100)	
	--declare @Mid_tt varchar(100)
	--SET @Cid=(SELECT Id FROM Company WHERE dwbh=@CompanyId)
	--IF(@ParentId='0')	
	--BEGIN
	--	set @Did_temp=(SELECT TOP(1) bmbh FROM Department WHERE substring(bmbh,1,2)=@CompanyId AND LEN(bmbh)=4 ORDER BY bmbh DESC)
	--	IF(@Did_temp='' OR @Did_temp IS NULL)		
	--		SET @Did=@CompanyId+'01'
	--	ELSE 
	--		BEGIN
	--			set @Mid_tt=CAST(CONVERT(int,SUBSTRING(@Did_temp,LEN(@Did_temp)-1,2)+1) AS VARCHAR(10))				
	--			IF(LEN(@Mid_tt)=1)
	--			SET @Mid_tt='0'+@Mid_tt				
	--			SET @Did=@CompanyId+@Mid_tt				
	--		END
	--END		
 --   ELSE 
 --   	BEGIN
 --   		SET @Did_temp=(SELECT TOP(1) bmbh FROM Department WHERE substring(bmbh,1,LEN(@ParentId))=@ParentId AND LEN(bmbh)=LEN(@ParentId)+2 ORDER BY bmbh DESC)  
 --   			IF(@Did_temp='' OR @Did_temp IS NULL)		
	--				SET @Did=@ParentId+'01'
	--			ELSE
	--				BEGIN
	--					set @Mid_tt=CAST(CONVERT(int,SUBSTRING(@Did_temp,LEN(@Did_temp)-1,2)+1) AS VARCHAR(10))		
	--					IF(LEN(@Mid_tt)=1)
	--					SET @Mid_tt='0'+@Mid_tt	
	--					SET @Did=@ParentId+@Mid_tt	
	--				END 
 --   	END 
    	
	--Insert Into Department(DName,CompanyId,Sort,bmbh) Values(@DName,@Cid,@Sort,@Did)
	--update [Department] set parentid=(isnull((select id from [Department] where bmbh=substring(d.bmbh,1,len(d.bmbh)-2)),0)) 
 --   from [Department] d  where bmbh is not null and parentid is null 
 
 DECLARE @Cid INT,@Cid_16 VARCHAR(50)
	DECLARE @Did VARCHAR(100),@Did_16 VARCHAR(100)
	declare @Did_temp varchar(100)	
	declare @Mid_tt varchar(100)

	SELECT @cid=Company.Id,@Cid_16=Company.Cid FROM Company,zzjg z WHERE dwbh=@CompanyId AND z.CompanyId=Company.Id
	IF isnull(@Cid_16,0)=0
	BEGIN
		RETURN
	END
	IF LEN(@Cid_16)=1
	BEGIN
		SET @Cid_16='0'+@Cid_16
	END
	
 --单位编号向LCM推送数用编号 Begin --------
 DECLARE @dwbh VARCHAR(50)
 SET @dwbh=(SELECT top 1 bh FROM zzjg z WHERE z.CompanyId=@CompanyId)
 
 IF(@ParentId='0')	
	BEGIN
		set @Did_temp=(SELECT TOP(1) erp_bh FROM Department WHERE companyid=@CompanyId AND ParentId=0 
		               ORDER BY erp_bh DESC)
		               
		IF(@Did_temp='' OR @Did_temp IS NULL)	
		BEGIN
			SET @Did=@dwbh+'01'			
		END							
		ELSE 
			BEGIN
				set @Mid_tt=CAST(CONVERT(int,RIGHT(@Did_temp,2)+1) AS VARCHAR(10))				
				IF(LEN(@Mid_tt)=1)
				SET @Mid_tt='0'+@Mid_tt	
				SET @Did=@dwbh+@Mid_tt	--新的部门编号					      			
			END
	END		
    ELSE 
    BEGIN
    	
    	DECLARE @Bmbh VARCHAR(50)
    	SET @Bmbh=(SELECT top 1 erp_bh FROM Department d WHERE d.id=@ParentId) --上级部门的编号
    	
    		SET @Did_temp=(SELECT TOP(1) erp_bh FROM Department WHERE 
    		parentid=@ParentId  ORDER BY erp_bh DESC)  
    			IF(@Did_temp='' OR @Did_temp IS NULL)		
					SET @Did=@Bmbh+'01'
				ELSE
					BEGIN 
						set @Mid_tt=CAST(CONVERT(int,RIGHT(@Did_temp,2)+1) AS VARCHAR(10))		
						IF(LEN(@Mid_tt)=1)
						SET @Mid_tt='0'+@Mid_tt	
						SET @Did=@Bmbh+@Mid_tt	--新的部门编号
					END 
    END
    
 --向LCM推送数用编号 END ---
 
 --单位编号ERP中用编号  BEGIN ---    
    IF(@ParentId='0')	
	BEGIN
		set @Did_temp=(SELECT TOP(1) bmbh FROM Department WHERE companyid=@CompanyId AND ParentId=0 
		               ORDER BY bmbh DESC)
		               
		IF(@Did_temp='' OR @Did_temp IS NULL)	
		BEGIN			
			SET @Did_16=@Cid_16+'01'
			
		END				
		ELSE 
			BEGIN
				set @Mid_tt=CAST(CONVERT(int,RIGHT(@Did_temp,2)+1) AS VARCHAR(50))				
				IF(LEN(@Mid_tt)=1)
				SET @Mid_tt='0'+@Mid_tt	
				--新的部门编号	
				SET @Did_16=@Cid_16+@Mid_tt
			END
	END		
    ELSE 
    BEGIN
    	
    	--DECLARE @Bmbh VARCHAR(50)
    	
    	SET @Bmbh=(SELECT top 1 bmbh FROM Department d WHERE d.id=@ParentId) --上级部门的编号
    	
    		SET @Did_temp=(SELECT TOP(1) bmbh FROM Department WHERE 
    		parentid=@ParentId  ORDER BY bmbh DESC)  
    		
    			IF(@Did_temp='' OR @Did_temp IS NULL)		
					SET @Did_16=@Bmbh+'01'					
				ELSE
					BEGIN 
						set @Mid_tt=CAST(CONVERT(int,RIGHT(@Did_temp,2)+1) AS VARCHAR(50))		
						IF(LEN(@Mid_tt)=1)
						SET @Mid_tt='0'+@Mid_tt	
						SET @Did_16=@Bmbh+@Mid_tt	--新的部门编号
					END 
    END
 --单位编号ERP中用编号  END ---        
       
    
    
    
-- 用单位编号生成为bmbh,在ERP中用于显示
Insert Into Department(DName,CompanyId,Sort,bmbh,ParentId,ERP_BH,ProtectCount,ProtectDays,TrackCount,TrackDays) 
Values(@DName,@Cid,@Sort, @Did_16,@ParentId,@Did,@ProtectCount,@ProtectDays,@TrackCount,@TrackDays)
	
RETURN